package jdbcTest;

import java.sql.*;

public class Test {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        OtherDemo();
//        quetryDemo();



    }



    private static void OtherDemo() throws SQLException, ClassNotFoundException {
        //1、初始化一些配置信息
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2、连接数据库的动作
        String url="jdbc:mysql://localhost:3306/demotest01 ?characterEncoding=UTF-8&serverTimezone=GMT%2B8";//数据库的地址
        String mysqlUser="root";//数据账号：root
        String mysqlPsd="root";//数据库密码：root
        Connection conn = DriverManager.getConnection(url, mysqlUser, mysqlPsd);
// 封装成册
        //3、执行删除 增加 修改
//        String sql="insert into student(sno,sname,ssex) values(120,'小道','男')";//按表名 列添加数据
        //4.增加
//        PreparedStatement pers=insertDemo(conn);
        //5.删除
//          PreparedStatement pers=deleteDemo(conn);
        //6.更新
        PreparedStatement pers=updateDemo(conn);

//        int i=1;

//        PreparedStatement pers=insertDemo(conn);
//        while( i<10){
//            PreparedStatement pers=insertDemo(conn);
//          PreparedStatement pers=deleteDemo(conn);
//        PreparedStatement preparedStatement = null;
//            int count =pers.executeUpdate();//删除 增加 修改 都是用它 会返回收到影响行数
//        String count = null;
//            System.out.println("count"+count+i);
            //记得关闭：最后使用的内容先关闭
//            pers.close();
//            i++;
//        }

        int count =pers.executeUpdate();
        System.out.println("count"+count);

        pers.close();
        conn.close();


    }

    private static PreparedStatement updateDemo(Connection conn) throws SQLException {
        String sql="update  student set sname=? where sno=?";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
//        preparedStatement.setInt(1,1);
        preparedStatement.setString(1,"丢失");
        preparedStatement.setInt(2,290);
//        preparedStatement.setInt(1,1);

        return preparedStatement;
    }

    private static PreparedStatement deleteDemo(Connection conn) throws SQLException {
        String sql="delete from student where sno>?";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setInt(1,4);
        return preparedStatement;
    }
    static int aaa=0;
    private static PreparedStatement insertDemo(Connection conn) throws SQLException {
        String sql="insert into student(sno,sname,ssex,sbirthday,class) values(?,?,?,?,?) ";//按表名 列添加数据
        //第一个参数表示的是 设置第几个问号的值 ?和设置的良要一致
        PreparedStatement preparedStatement = conn.prepareStatement(sql);

            preparedStatement.setInt(1, 286+aaa++);
            preparedStatement.setString(2, "信息");
            preparedStatement.setString(3, "女");
            preparedStatement.setString(4, "2020-05-10");
            preparedStatement.setInt(5, 12886);

        return preparedStatement;
    }

    public static void quetryDemo(String[] args) throws ClassNotFoundException, SQLException {
        //1、初始化一些配置信息
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2、连接数据库的动作
        String url="jdbc:mysql://localhost:3306/demotest01 ?characterEncoding=UTF-8&serverTimezone=GMT%2B8";//数据库的地址
        String mysqlUser="root";//数据账号：root
        String mysqlPsd="root";//数据库密码：root
        Connection conn = DriverManager.getConnection(url, mysqlUser, mysqlPsd);

        //3、执行sql语句对象
//        conn.createStatement();//执行静态sql语句 （这里的静态表示语句写死的意思）
//        conn.prepareStatement();//动态执行sql语句 预编译 这种目前来书比较安全
//        Statement statement = conn.createStatement();

        //预编译格式
        String sql="select * from student where sno>? and sname like ?";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setInt(1,0);
        preparedStatement.setString(2,"%李%");
        ResultSet resultSet = preparedStatement.executeQuery();//执行查询
        //执行语句一般就使用者两个
//        statement.executeQuery();//执行查询语句 返回一类似与迭代器的东西
//        statement.executeUpdate();//执行更新 删除 增加的sql语句 会返回收到影响的行数
//        行 INSERT、UPDATE 或 DELETE 语句以及 SQL DDL（数据定义语言）语句，
        //4、执行插叙语句之后 会返回一个结果集对象
        //变量类型补全 ctrl+alt+v
//        ResultSet resultSet = statement.executeQuery(sql);
        while(resultSet.next()) {//移动这个右表 获取第一条数据 获取第二条数据
            //都是学生这个表中的列名
//            int sno = resultSet.getInt("sno");
//            String sname = resultSet.getString("sname");
////            int class = resultSet.getInt("class");
//            String sex = resultSet.getString("ssex");
//            System.out.println(sno + "," + sname + "," + sex);
            int sno = resultSet.getInt(1);
            String sname = resultSet.getString(2);
//            int class = resultSet.getInt("class");
            String sex = resultSet.getString(3);
            String sbirthday=resultSet.getString(4);
            Date Sbirthday1 = resultSet.getDate(4);
            System.out.println(sno + "," + sname + "," + sex+","+sbirthday+","+Sbirthday1);
        }
        //记得关闭：最后使用的内容先关闭
             resultSet.close();
             preparedStatement.close();
             conn.close();


    }
}
